package com.fpt.quanlysanbong.dao;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.fpt.quanlysanbong.domain.ServiceOrder;

public class ServiceOrderDao extends BaseDao {

	public List<ServiceOrder> getListServiceOrder(int orderId) {
		List<ServiceOrder> lst = new ArrayList<ServiceOrder>();

		String sql = "Select * From dbo.ServiceOrder where datediff(d,ordered_date,getdate()) <=0 and orderId = "
				+ orderId;
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);

			while (rs.next()) {
				ServiceOrder so = new ServiceOrder();

				so.setId(rs.getInt("id"));
				so.setService(new ServiceDao().getSoccerService(rs.getInt("serviceId")));
				so.setOrderId(new SoccerOrderDao().getSoccerOrder(orderId));
				so.setUnitPrice(rs.getFloat("unitPrice"));
				so.setNumber_items(rs.getInt("number_items"));
				so.setOrdered_date(rs.getDate("ordered_date"));

				lst.add(so);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return lst;
	}

	public List<ServiceOrder> getListHistoryServiceOrder(String date, int orderId) {
		List<ServiceOrder> lst = new ArrayList<ServiceOrder>();

		if (StringUtils.isBlank(date)) {
			return lst;
		}

		try {
			SimpleDateFormat f = new SimpleDateFormat("dd/MM/yyyy");

			Date checkDate = new Date(f.parse(date).getTime());

			String sql = "Select * From dbo.ServiceOrder where datediff(d,ordered_date,'" + checkDate
					+ "') >=0 and orderId = " + orderId;
			ResultSet rs = conn.createStatement().executeQuery(sql);

			while (rs.next()) {
				ServiceOrder so = new ServiceOrder();

				so.setId(rs.getInt("id"));
				so.setService(new ServiceDao().getSoccerService(rs.getInt("serviceId")));
				so.setOrderId(new SoccerOrderDao().getSoccerOrder(orderId));
				so.setUnitPrice(rs.getFloat("unitPrice"));
				so.setNumber_items(rs.getInt("number_items"));
				so.setOrdered_date(rs.getDate("ordered_date"));

				lst.add(so);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return lst;
	}

	public boolean addService(ServiceOrder serviceOrder) {
		boolean success = false;

		String sql1 = "Insert into dbo.ServiceOrder(orderId, serviceId, unitPrice, number_items,ordered_date) values (?,?,?,?,getdate())";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1);
			prst.setInt(1, serviceOrder.getOrderId().getId());
			prst.setInt(2, serviceOrder.getService().getId());
			prst.setFloat(3, serviceOrder.getUnitPrice());
			prst.setInt(4, serviceOrder.getNumber_items());

			int affectedRows = prst.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException("Creating Service failed, no rows affected.");
			}
			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return success;
	}

	public boolean editService(ServiceOrder serviceOrder) {
		boolean success = false;

		String sql1 = "UPDATE dbo.ServiceOrder SET serviceId = ?, unitPrice = ?, number_items = ? WHERE id = ?";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1);

			prst.setInt(1, serviceOrder.getService().getId());
			prst.setFloat(2, serviceOrder.getUnitPrice());
			prst.setInt(3, serviceOrder.getNumber_items());
			prst.setInt(4, serviceOrder.getId());

			prst.executeUpdate();
			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return success;
	}

	public boolean deleteService(int id) {
		boolean success = false;

		String sql1 = "DELETE FROM dbo.ServiceOrder WHERE id = ?  ";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1);
			prst.setInt(1, id);

			prst.executeUpdate();
			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return success;
	}
}
